package mwa.cs.hh.ctrl;

import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import mwa.cs.dao.ArDAO;
import mwa.cs.dao.CustHHDAO;
import mwa.cs.dao.CustMonthlyDAO;
import mwa.cs.dao.GenDocDAO;
import mwa.cs.dao.ReaderDAO;
import mwa.cs.dao.RouteDAO;
import mwa.cs.dao.RouteScheduleMasterDAO;
import mwa.cs.dao.SMErrorLogDAO;
import mwa.cs.dao.UserLoginDAO;
import mwa.cs.dao.ZoneDAO;
import mwa.cs.database.Database;
import mwa.cs.model.CustHH;
import mwa.cs.model.ObjectPrepareDataTemp;
import mwa.cs.model.Reader;
import mwa.cs.model.Route;
import mwa.cs.model.RouteScheduleMaster;
import mwa.cs.util.JDate;

/**
 * Servlet implementation class CS31000
 */
public class CS31000Srvl extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private int vnSiteId;
	private int vnUserId;
	private int vnZoneId;
	private String vaUrlAddress;
	private String vaAgentBrowser;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CS31000Srvl() {
        super();
    }

    public void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
		vnUserId = Integer.parseInt(request.getSession().getAttribute("user_id").toString());

		// get URL_ADDRESS, AGENT_BROWSER
		vaUrlAddress = request.getParameter("url_address");
		vaAgentBrowser = request.getParameter("agent_browser");
		String vaZone = request.getParameter("zone");
		String vaZoneFromRSM = request.getParameter("zone_rsm");
		String vaPrepareStatus = "", vaPageSetting = "1";
    	if (vaZone == null || vaZone.equals("")) {
    		vaZone = "0";
    	}
		vnZoneId = Integer.parseInt(vaZone);
		String vaButtonSaveStep1 = request.getParameter("btn_save_step1");
    	String vaButtonFindRouteStep2 = request.getParameter("btn_findRoute_step2");
    	String vaButtonSaveStep2 = request.getParameter("btn_save_step2");
    	String vaButtonSaveAllStep2 = request.getParameter("btn_saveall_step2");
    	String vaCheckSaveDataByWho = request.getParameter("save_by");
    	String vaBtnCancelPrepareDataStep1 = request.getParameter("btn_cancel_prepareDataStep1");
    	String vaUpdateDownload = request.getParameter("updateDownload");
    	String vaDataToUpdate = request.getParameter("vaDataToUpdate");
    	String vaErrorCancel = "no";
    	String vaButtonCancel = "show";
    	
    	// OnClick Save Button In Step 1
    	if (vaButtonSaveStep1 != null && vaButtonSaveStep1.equals("yes")){
    		vaPrepareStatus = prepareDataOfTheMonth(request, response);
    	} else if (vaButtonFindRouteStep2 != null && vaButtonFindRouteStep2.equals("yes")){
    		prepareDataForSetReader(request);
    		vaPageSetting = "2";
    	} else if (vaButtonSaveStep2 != null && (vaButtonSaveStep2.equals("yes") || vaButtonSaveAllStep2.equals("yes"))){
    		setReaderToRoute(request,vaButtonSaveAllStep2);
    		vaPageSetting = "2";
    	} else if (vaCheckSaveDataByWho != null && vaCheckSaveDataByWho.equals("yes")) {
    		showSaveDataByWho(request, response);
    		return;
    	} else if (vaZoneFromRSM != null && vaZoneFromRSM.equals("yes")) {
    		loadZoneInStep2(request, response);
    		return;
    	} else if (vaBtnCancelPrepareDataStep1 != null && vaBtnCancelPrepareDataStep1.equals("yes")){
    		cancePrepareData(vaErrorCancel);
    	} else if (vaUpdateDownload != null && vaUpdateDownload.equals("yes")){
    		updateDownloadDate(request, vaDataToUpdate);
    		vaPageSetting = "3";
    	}
    	// loadZone in table step1
    	loadZoneDataForStepOne(request, response, vaButtonCancel);
    	// set attribute for CS31000.jsp
    	request.setAttribute("vaPageSetting", vaPageSetting); 
    	request.setAttribute("vaErrorCancel", vaErrorCancel);
    	request.setAttribute("vaPrepareStatus", vaPrepareStatus);
    	request.setAttribute("vnZoneId", vnZoneId);
		// forward back to cs31001.jsp
    	RequestDispatcher rd = request.getRequestDispatcher("CS31000.jsp");
    	rd.forward(request, response);
    }
    
    // on click cancel button
    public void cancePrepareData (String vaErrorCancel){
		Database db = new Database();
    	CustHHDAO voCustHHManagement = new CustHHDAO(db);
    	ArDAO voArDAO = new ArDAO(db);
    	RouteScheduleMasterDAO voRouteScheduleMasterManagement = new RouteScheduleMasterDAO(db);
		String vaMaxHHMonth = voCustHHManagement.findMaxMonth(vnSiteId);
		String vaMaxHHYear = voCustHHManagement.findMaxYear(vnSiteId);
		String vaARMaxMonth = voArDAO.findMaxMonth(vnSiteId);
		String vaARMaxYear = voArDAO.findMaxYear(vnSiteId);
		// cancel prepare data by max month and max year
		if ( vaMaxHHYear.equals(vaARMaxYear)){
			if( Integer.parseInt(vaMaxHHMonth) > Integer.parseInt(vaARMaxMonth)){
				// กรณีปีเดียวกัน
				try {
					db.beginTransaction();
					voRouteScheduleMasterManagement.deletePrepareData( vaMaxHHMonth, vaMaxHHYear, vnSiteId);
            		voCustHHManagement.deletePrepareData( vaMaxHHMonth, vaMaxHHYear, vnSiteId);
            		db.commit();
				} catch (Exception e) {
					SMErrorLogDAO.getInstance().addLog(0, "CS31000", JDate.getDate(), JDate.getTime(),vnUserId, "CS31000Srvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);	
					db.rollback();
				}		
			}
		} else if ( Integer.parseInt(vaMaxHHYear) > Integer.parseInt(vaARMaxYear)){
			// กรณีข้ามปี
			try {
				db.beginTransaction();
				voRouteScheduleMasterManagement.deletePrepareData( vaMaxHHMonth, vaMaxHHYear, vnSiteId);
        		voCustHHManagement.deletePrepareData( vaMaxHHMonth, vaMaxHHYear, vnSiteId);
        		db.commit();
			} catch (Exception e) {
				SMErrorLogDAO.getInstance().addLog(0, "CS31000", JDate.getDate(), JDate.getTime(),vnUserId, "CS31000Srvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);
				db.rollback();
			}	
		// กรณียกเลิกการเตรียมข้อมูลไม่ได้
		} else if ( vaMaxHHYear.equals(vaARMaxYear) && vaMaxHHMonth.equals(vaARMaxMonth)){
			vaErrorCancel = "yes";
		}
		db.close();
    }
    
	// update download date when on click download button
    public void updateDownloadDate (HttpServletRequest request, String vaDataToUpdate){
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
		vnUserId = Integer.parseInt(request.getSession().getAttribute("user_id").toString());

    	String[] vaDataTemp = vaDataToUpdate.split(":");
    	Database db = new Database();
    	CustHHDAO voCustHHManagement = new CustHHDAO(db);
    	String vaMaxMonth = voCustHHManagement.findMaxMonth(vnSiteId);
    	String vaMaxYear = voCustHHManagement.findMaxYear(vnSiteId);
    	RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
    	int vaDownloadAmount = 0;
    	int vnRouteId = 0;
		//Get RouteId by site_id , zone_id  , route_id ,  period_month , period_year 
		vnRouteId = voRSMManagement.getRouteIdByReaderId(vnSiteId, Integer.parseInt(vaDataTemp[0]), Integer.parseInt(vaDataTemp[1]), vaMaxMonth, vaMaxYear);
    	try {
    		db.beginTransaction();
    		// Update DownloadDate in CustHH
    		voCustHHManagement.updateDownloadDate(vnSiteId, Integer.parseInt(vaDataTemp[0]),vnRouteId, vaMaxMonth, vaMaxYear);
    		db.commit();
		} catch (Exception e) {
			SMErrorLogDAO.getInstance().addLog(0, "CS31000", JDate.getDate(), JDate.getTime(),vnUserId, "CS31000Srvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);
			db.rollback();
		}
    	
    	try {
    		db.beginTransaction();
    		//Count account Of Route by site_id ,   zone_id ,   route_id ,   period_month ,   period_year
    		vaDownloadAmount = voCustHHManagement.countAccountAmount(vnSiteId, Integer.parseInt(vaDataTemp[0]),vnRouteId, vaMaxMonth, vaMaxYear);
    		// Update DownloadDate in RouteScheduleMaster
    		voRSMManagement.updateDownloadDate(vnSiteId, Integer.parseInt(vaDataTemp[0]), Integer.parseInt(vaDataTemp[1]), vaMaxMonth, vaMaxYear, vaDownloadAmount);
    		db.commit();
    	} catch (Exception e) {
    		SMErrorLogDAO.getInstance().addLog(0, "CS31000", JDate.getDate(), JDate.getTime(),vnUserId, "CS31000Srvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);
    		db.rollback();
    	} finally {
    		db.close();
    	}
	}
    
    @SuppressWarnings("unchecked")
    private void showSaveDataByWho(HttpServletRequest request, HttpServletResponse response) throws IOException {
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
    	
    	String[] vaMessage = null, vaValue = null;
		Database db = new Database();
    	CustHHDAO voCustHHManagement = new CustHHDAO(db);
		ArDAO voArManagement = new ArDAO(db);
		String vaMaxMonth = voCustHHManagement.findMaxMonth(vnSiteId);
		String vaMaxYear = voCustHHManagement.findMaxYear(vnSiteId);
		int vnCountCustHH = voCustHHManagement.CountCustHHInPeriod(vnSiteId, vaMaxMonth, vaMaxYear);
		int vnCountAr = voArManagement.CountArInPeriod(vnSiteId, vaMaxMonth, vaMaxYear);
		vaValue = new String[1];
		vaMessage = new String[1];
		vaValue[0] = "user";
		if (vnCountCustHH == 0 || vnCountCustHH == vnCountAr) {
			vaMessage[0] = "";
		} else {
			CustMonthlyDAO voCustMonthlyManagement = new CustMonthlyDAO(db);
			String vaPeriodMonth = voCustMonthlyManagement.findMaxMonth(vnSiteId);
			String vaPeriodYear = voCustMonthlyManagement.findMaxYear(vnSiteId);
			String vaPeriodNextMonth = JDate.getNextMonth(vaPeriodMonth, vaPeriodYear);
			String vaPeriodNextYear = JDate.getYearOfNextMonth(vaPeriodMonth, vaPeriodYear);
			String[] voInsertUser = voCustHHManagement.findInsertUser(vnSiteId, vaPeriodNextMonth, vaPeriodNextYear);
			if (voInsertUser != null) {
				UserLoginDAO voUserLoginManagement = new UserLoginDAO(db);
				String message = "บันทึกข้อมูลโดย" 
				+ voUserLoginManagement.findNameByUserId(vnSiteId, Integer.parseInt(voInsertUser[0])) 
				+ " วันที่ "
				+ JDate.showDate(voInsertUser[1])
				+ " เวลา "
				+ JDate.showTime(voInsertUser[2]);
				vaMessage[0] = message;
			} else {
				vaMessage[0] = "";
			}
		}
	
		PrintWriter out = response.getWriter();
		try {
			JSONObject voObject = new JSONObject();
			JSONArray voList = new JSONArray();
			if (vaMessage != null) {
				for (int i = 0; i < vaMessage.length; i ++) {
					voList.add(vaMessage[i]);
				}
			}
			voObject.put("messages", voList);
			JSONArray voList2 = new JSONArray();
			if (vaValue != null) {
				for (int i = 0; i < vaValue.length; i ++) {
					voList2.add(vaValue[i]);
				}
			}
			voObject.put("values", voList2);
			out.print(voObject.toJSONString());
		} finally {
			out.close();
		}
    }
    
    @SuppressWarnings("unchecked")
    private void loadZoneInStep2(HttpServletRequest request, HttpServletResponse response) throws IOException {
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
    	
    	String[] vaMessage = null, vaValue = null;
		Database db = new Database();
		CustHHDAO voCustHHManagement = new CustHHDAO(db);
		String vaMaxMonth = voCustHHManagement.findMaxMonth(vnSiteId);
		String vaMaxYear = voCustHHManagement.findMaxYear(vnSiteId);
		RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
		List<Map<String, Object>> voResult = voRSMManagement.findZoneBySiteDistinct(vnSiteId, vaMaxMonth, vaMaxYear);
		vaValue = new String[voResult.size()];
		vaMessage = new String[voResult.size()];
		for (int i = 0; i < voResult.size(); i++) {
			Map<String, Object> voObject = voResult.get(i);	
			vaValue[i] = ((BigDecimal) voObject.get("ZONE_ID")).toString();
			vaMessage[i] = (String) voObject.get("ZONE_NAME");		
		}
		PrintWriter out = response.getWriter();
		try {
			JSONObject voObject = new JSONObject();
			JSONArray voList = new JSONArray();
			if (vaMessage != null) {
				for (int i = 0; i < vaMessage.length; i ++) {
					voList.add(vaMessage[i]);
				}
			}
			voObject.put("messages", voList);
			JSONArray voList2 = new JSONArray();
			if (vaValue != null) {
				for (int i = 0; i < vaValue.length; i ++) {
					voList2.add(vaValue[i]);
				}
			}
			voObject.put("values", voList2);
			out.print(voObject.toJSONString());
		} finally {
			db.close();
			out.close();
		}
    }
    
	private void loadZoneDataForStepOne(HttpServletRequest request, HttpServletResponse response, String vaButtonCancel){
		vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());

		Database db = new Database();
		String vaCurrentMonth = null, vaCurrentYear = null, vaShowButton = null;
    	ArDAO voArDAO = new ArDAO(db);
		CustHHDAO voCustHHManagement = new CustHHDAO(db);
		ArDAO voArManagement = new ArDAO(db);
		String vaMaxMonth = voCustHHManagement.findMaxMonth(vnSiteId);
		String vaMaxYear = voCustHHManagement.findMaxYear(vnSiteId);
		String vaARMaxMonth = voArDAO.findMaxMonth(vnSiteId);
		String vaARMaxYear = voArDAO.findMaxYear(vnSiteId);
		int vnCountCustHH = voCustHHManagement.CountCustHHInPeriod(vnSiteId, vaMaxMonth, vaMaxYear);
		int vnCountAr = voArManagement.CountArInPeriod(vnSiteId, vaMaxMonth, vaMaxYear);		
		if ( vaMaxYear.equals(vaARMaxYear) && vaMaxMonth.equals(vaARMaxMonth)){
			vaButtonCancel = "hide";
		}
		// ดูเดือน/ปี ที่จะแสดงให้แสดงตอนไหน
		if (vnCountCustHH == 0) {
			vaCurrentMonth= JDate.getMonthName(Integer.parseInt(JDate.getMonth()));
			vaCurrentYear = JDate.getYear();
		} else if (vnCountCustHH != vnCountAr) {
			vaCurrentMonth = JDate.getMonthName(Integer.parseInt(vaMaxMonth));
			vaCurrentYear = vaMaxYear;
		} else {
			vaCurrentMonth = JDate.getMonthName(Integer.parseInt(JDate.getNextMonth(vaMaxMonth, vaMaxYear)));
			vaCurrentYear = JDate.getYearOfNextMonth(vaMaxMonth, vaMaxYear);
		}
		// check show ปุ่มเตรียมข้อมูลการอ่านมาตร
		if (vnCountCustHH == 0 || vnCountCustHH == vnCountAr) {
			vaShowButton = "yes"; // ยังไม่ได้เตรียมข้อมูลอ่านมาตร
		} else {
			vaShowButton = "no"; // เตรียมข้อมูลอ่านมาตรไปแล้วไม่สามารถกดปุ่มซ้ำได้
		}
		
		// load zone in step1
		List<ObjectPrepareDataTemp> voObjectPrepareDataTempList = new ArrayList<ObjectPrepareDataTemp>();
		if (vaShowButton == "yes"){
			// load all zone from route
			ZoneDAO voZone = new ZoneDAO(db);
			RouteDAO voRoute = new RouteDAO(db);
			List<Map<String, Object>> voResult = voZone.findZoneBySite(vnSiteId);	
			for (int i = 0; i < voResult.size(); i++) {
				ObjectPrepareDataTemp voObjectPrepareDataTemp = new ObjectPrepareDataTemp();
				Map<String, Object> voObject = voResult.get(i);
				List<Map<String, Object>> voRouteAmount = voRoute.findRouteByZone(vnSiteId, Integer.parseInt(((BigDecimal) voObject.get("ZONE_ID")).toString()));
				voObjectPrepareDataTemp.setVaZoneId(((BigDecimal) voObject.get("ZONE_ID")).toString()); // count route amount and set In ZoneId
				voObjectPrepareDataTemp.setVaZoneName((String) voObject.get("ZONE_NAME"));
				voObjectPrepareDataTemp.setVaRouteAmount(String.valueOf(voRouteAmount.size()));
		    	voObjectPrepareDataTemp.setVaRoutePrepareData("0");
		    	voObjectPrepareDataTemp.setVaRouteDownload("0");
				voObjectPrepareDataTempList.add(voObjectPrepareDataTemp);
			}
		} else {	    	
	    	// load all zone from RSM in recent period_month and period_year
		    RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
	    	// ZoneName not Distinct
		    List<Map<String, Object>> voZoneOfSite = voRSMManagement.findZoneBySiteDistinct(vnSiteId, vaMaxMonth, vaMaxYear);
		    for (int i = 0; i < voZoneOfSite.size(); i++) {
		    	ObjectPrepareDataTemp voObjectPrepareDataTemp = new ObjectPrepareDataTemp();
		    	Map<String, Object> voObject = voZoneOfSite.get(i);
		    	int vaZoneOfSite = Integer.parseInt(((BigDecimal) voObject.get("ZONE_ID")).toString());
		    	String vaZoneName = voObject.get("ZONE_NAME").toString();
		    	List<Map<String, Object>> voPrepareDataOfZone = voRSMManagement.findPrepareDataOfZone(vnSiteId, vaZoneOfSite, vaMaxMonth, vaMaxYear);
		    	List<Map<String, Object>> voRouteOfZone = voRSMManagement.findRouteByZone(vnSiteId, vaZoneOfSite, vaMaxMonth, vaMaxYear);
		    	List<Map<String, Object>> voDownloadOfZone = voRSMManagement.findTotalDownloadByZone(vnSiteId, vaZoneOfSite, vaMaxMonth, vaMaxYear);
		    	voObjectPrepareDataTemp.setVaZoneId(String.valueOf(vaZoneOfSite));
		    	voObjectPrepareDataTemp.setVaZoneName(vaZoneName);
		    	voObjectPrepareDataTemp.setVaRouteAmount(String.valueOf(voRouteOfZone.size()));
		    	voObjectPrepareDataTemp.setVaRoutePrepareData(String.valueOf(voPrepareDataOfZone.size()));
		    	voObjectPrepareDataTemp.setVaRouteDownload(String.valueOf(voDownloadOfZone.size()));		   
		    	voObjectPrepareDataTempList.add(voObjectPrepareDataTemp);
		    }
		}	
		
		// query data for Step 3
		String IsEmptyData = "no";
		RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
    	String vaHHPeriodMonth = voCustHHManagement.findMaxMonth(vnSiteId);
    	String vaHHPeriodYear = voCustHHManagement.findMaxYear(vnSiteId);
    	List<Map<String, Object>> voRouteScheduleMaster = voRSMManagement.findPrepareDataGroupByReader(vnSiteId, vaHHPeriodMonth, vaHHPeriodYear);
    	List<Reader> voReaderList = new ArrayList<Reader>();
    	for (int i = 0; i < voRouteScheduleMaster.size(); i++){
    		String vaDownloadStatus ="readyLoad";
    		Map<String, Object> voObject = voRouteScheduleMaster.get(i);
    		Reader voReaderObject = new Reader();
    		voReaderObject.setVnSiteId( Integer.parseInt( voObject.get("ZONE_ID").toString()));
    		voReaderObject.setVnReaderId( Integer.parseInt( voObject.get("READER_ID").toString()));
    		voReaderObject.setVaReaderName( voObject.get("READER_NAME").toString());
        	
    		// check download|print status for Step3
    		int vnRouteId = voRSMManagement.getRouteIdByReaderId(vnSiteId, voReaderObject.getVnSiteId(), voReaderObject.getVnReaderId(), vaMaxMonth, vaMaxYear);
    		//Count account Of Route by site_id ,   zone_id ,   route_id ,   period_month ,   period_year
        	String vaDownloadDate = voRSMManagement.findDownloadDate(vnSiteId, voReaderObject.getVnSiteId(), vnRouteId, vaHHPeriodMonth, vaHHPeriodYear);
        	if ( vaDownloadDate != null){
            	vaDownloadStatus = "alreadyLoad";
        	} 
        	voReaderObject.setVaStatus(vaDownloadStatus);	
    		voReaderList.add(voReaderObject);
    	}
    	if ( voRouteScheduleMaster.size() == 0){
    		IsEmptyData = "yes";
    	}
    	request.setAttribute("IsEmptyData", IsEmptyData);
		request.setAttribute("voReaderList", voReaderList);
    	request.setAttribute("vaButtonCancel",vaButtonCancel);
	    request.setAttribute("voObjectPrepareDataTempList", voObjectPrepareDataTempList);
	    request.setAttribute("vaShowButton", vaShowButton); 
	    request.setAttribute("currentMonth", vaCurrentMonth);
	    request.setAttribute("currentYear", vaCurrentYear);
	    db.close();
    }
    
	private String prepareDataOfTheMonth(HttpServletRequest request, HttpServletResponse response) {
		vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
		vnUserId = Integer.parseInt(request.getSession().getAttribute("user_id").toString());

		String vaPrepareStatus = null;
    	Database db = new Database();    	
    	GenDocDAO voGenDocManagement = new GenDocDAO(db);
		CustHHDAO voCustHHManagement = new CustHHDAO(db);
		CustMonthlyDAO voCustMonthlyManagement = new CustMonthlyDAO(db);
    	try {
    		db.beginTransaction();
    		String vaPeriodMonth = voCustMonthlyManagement.findMaxMonth(vnSiteId);
    		String vaPeriodYear = voCustMonthlyManagement.findMaxYear(vnSiteId);
    		voCustHHManagement.InsertCustHHPrepareData(vnSiteId, vaPeriodMonth, vaPeriodYear, vnUserId, JDate.getDate(),JDate.getTime());
    		String vaPeriodNextMonth = JDate.getNextMonth(vaPeriodMonth, vaPeriodYear);
    		String vaPeriodNextYear = JDate.getYearOfNextMonth(vaPeriodMonth, vaPeriodYear);
    		List<Map<String, Object>> voResult = voCustHHManagement.findCustHH(vnSiteId, vaPeriodNextMonth, vaPeriodNextYear); 
    		for (int i = 0; i < voResult.size(); i++) {
    			Map<String, Object> voTmp = voResult.get(i);
    			String vaBillNumber = voGenDocManagement.generatingNextBNDoc(vnSiteId, vaPeriodNextMonth, vaPeriodNextYear);
    			voCustHHManagement.updateBillNumberInCustHH(vnSiteId, (String) voTmp.get("ACCOUNT"), vaBillNumber, 
    					vaPeriodNextMonth, vaPeriodNextYear, vnUserId, JDate.getDate(),JDate.getTime());
    		}
    		RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
    		voRSMManagement.InsertPrepareData(vnSiteId, vaPeriodMonth, vaPeriodYear, vnUserId, JDate.getDate(),JDate.getTime());
    		db.commit();
    		vaPrepareStatus = "success";
    	} catch (Exception e) {
    		db.rollback();
			SMErrorLogDAO.getInstance().addLog(0, "CS31000", JDate.getDate(), JDate.getTime(),vnUserId, "CS31000Srvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);
			vaPrepareStatus = "fail";
    	} finally {
    		db.close(); 
    	}
    	return vaPrepareStatus;
    }
    
    private void prepareDataForSetReader(HttpServletRequest request) {
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());

    	Database db = new Database();
    	ZoneDAO voZoneManagement = new ZoneDAO(db);
    	RouteDAO voRouteManagement = new RouteDAO(db);
    	ReaderDAO voReaderManagement = new ReaderDAO(db);
    	RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
    	CustMonthlyDAO voCustMonthlyManagement = new CustMonthlyDAO(db);
    	CustHHDAO voCustHHManagement = new CustHHDAO(db);
    	// ค้นหาชื่อของเขตนั้น zone_name from zone where zone_id 
    	String vaZoneName = voZoneManagement.findZoneNameBySiteAndZoneId(vnSiteId, vnZoneId);
    	// ค้นหา reader_id, reader_name from reader
    	Map<String, String> voAllReader = new HashMap<String,String>(); 
		List<Map<String, Object>> voResult2 = voReaderManagement.findReaderBySite(vnSiteId);
		for (int i = 0; i < voResult2.size(); i++) {
			Map<String, Object> voObject = voResult2.get(i);
			voAllReader.put(voObject.get("READER_ID").toString(), (String) voObject.get("READER_NAME"));
		}
		// ค้นหาเส้นทางทั้งหมดในระบบและ default reader ของแต่ละเส้นทางนั้นเพื่อนำไปใช้ใส่เส้นทางที่มีการเตรียมข้อมูลไว้
		Map<String,Object> voAllRoute = new HashMap<String,Object>();
    	List<Map<String, Object>> voResult = voRouteManagement.findRouteByZone(vnSiteId, vnZoneId);
    	for (int i = 0; i < voResult.size(); i++) {
			Map<String, Object> voObject = voResult.get(i);
			Route voRoute = new Route();
			voRoute.setVnRouteId(Integer.parseInt(((BigDecimal) voObject.get("ROUTE_ID")).toString()));
			voRoute.setVaRouteDescription((String) voObject.get("ROUTE_DESCRIPTION"));
			voRoute.setVnReaderId(Integer.parseInt(((BigDecimal) voObject.get("READER_ID")).toString()));
			voAllRoute.put(((BigDecimal) voObject.get("ROUTE_ID")).toString(), voRoute);
		}
    	// ค้นหาเส้นทางที่ถูกเตรียมข้อมูลไว้เพื่อนำไปแสดง
    	String vaCustHHPeriodMonth = voCustHHManagement.findMaxMonth(vnSiteId);
    	String vaPeriodMonth = voCustMonthlyManagement.findMaxMonth(vnSiteId);
		String vaPeriodYear = voCustMonthlyManagement.findMaxYear(vnSiteId);
		String vaPeriodRecentMonth = "";
		String vaPeriodRecentYear = "";
		if (!vaCustHHPeriodMonth.equals(vaPeriodMonth)) {
			vaPeriodRecentMonth = JDate.getNextMonth(vaPeriodMonth, vaPeriodYear);
			vaPeriodRecentYear = JDate.getYearOfNextMonth(vaPeriodMonth, vaPeriodYear);
		} else {
			vaPeriodRecentMonth = vaPeriodMonth;
			vaPeriodRecentYear = vaPeriodYear;
		}
    	List<Map<String, Object>> voResult3 = voRSMManagement.findReaderIdInRouteScheduleMaster(vnSiteId, vnZoneId, vaPeriodRecentMonth, vaPeriodRecentYear);
    	Route[] voRoute = new Route[voResult3.size()]; // all route from route where zone_id, voRoute.size == finding route_size in zone_id
    	Map<String,String> voRSMReader = new HashMap<String,String>(); // route, reader_id from route_schedule_master
    	String[] vaTotalDownload = new String[voResult3.size()];
    	for (int i = 0; i < voRoute.length; i ++) {
    		Map<String, Object> voObject = voResult3.get(i);
    		voRoute[i] = new Route();
    		voRoute[i].setVnRouteId(Integer.parseInt(((BigDecimal) voObject.get("ROUTE_ID")).toString()));
    		voRoute[i].setVaRouteDescription(((Route) voAllRoute.get(Integer.toString(voRoute[i].getVnRouteId()))).getVaRouteDescription());
    		voRoute[i].setVnReaderId(((Route) voAllRoute.get(Integer.toString(voRoute[i].getVnRouteId()))).getVnReaderId());
    		voRSMReader.put(((BigDecimal) voObject.get("ROUTE_ID")).toString(), ((BigDecimal) voObject.get("READER_ID")).toString());
    		vaTotalDownload[i] = (String) voObject.get("TOTAL_DOWNLOAD").toString();
    		//voRSMDownload.put(((BigDecimal) voObject.get("ROUTE_ID")).toString(), ((BigDecimal) voObject.get("TOTAL_DOWNLOAD")).toString());
    	}
    	db.close();
    	request.setAttribute("vaZoneName", vaZoneName);
		request.setAttribute("voRoute", voRoute);
		request.setAttribute("voMapReader", voAllReader);
		request.setAttribute("voRSMReader", voRSMReader);
		request.setAttribute("vaTotalDownload", vaTotalDownload);
    }
    
    private void setReaderToRoute(HttpServletRequest request,String vaButtonSaveAllStep2) {
    	vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());

    	Database db = new Database();
    	RouteDAO voRouteManagement = new RouteDAO(db);
    	RouteScheduleMasterDAO voRSMManagement = new RouteScheduleMasterDAO(db);
    	CustMonthlyDAO voCustMonthlyManagement = new CustMonthlyDAO(db);
    	CustHHDAO voCustHHManagement = new CustHHDAO(db);
    	String vaHHPeriodMonth = voCustHHManagement.findMaxMonth(vnSiteId);
    	String vaHHPeriodYear = voCustHHManagement.findMaxYear(vnSiteId);
    	String vaPeriodMonth = voCustMonthlyManagement.findMaxMonth(vnSiteId);
		String vaPeriodYear = voCustMonthlyManagement.findMaxYear(vnSiteId);
		String vaPeriodRecentMonth = JDate.getNextMonth(vaPeriodMonth, vaPeriodYear);
		String vaPeriodRecentYear = JDate.getYearOfNextMonth(vaPeriodMonth, vaPeriodYear);
    	List<Map<String, Object>> voResult3 = voRSMManagement.findReaderIdInRouteScheduleMaster(vnSiteId, vnZoneId, vaPeriodRecentMonth, vaPeriodRecentYear);

    	String[] voSelection = new String[voResult3.size()];
		for (int i = 0; i < voSelection.length; i++) {
			voSelection[i] = request.getParameter("selection" + (i + 1));
			if (voSelection[i] == null) {
				continue;
			}
			String vnRouteIdReaderId[] = voSelection[i].split(":");
			int vnRouteID = Integer.parseInt(vnRouteIdReaderId[0]);
			int vnReaderID = Integer.parseInt(vnRouteIdReaderId[1]);
			voRSMManagement.updateReaderInRSM(vnSiteId, vnZoneId, vaPeriodRecentMonth, vaPeriodRecentYear, vnRouteID, vnReaderID);
			//System.out.println((i+1) + ": " + voSelection[i]);
		}		
		
		// select data in RSM where READER_ID = 0
		if (vaButtonSaveAllStep2.equals("yes")){
	    	List<Map<String, Object>> voZoneResult = voRSMManagement.findZoneBySite(vnSiteId, vaPeriodRecentMonth, vaPeriodRecentYear);
	    	int vnReaderIdOfRoute = 0;
	    	for (int i = 0; i < voZoneResult.size(); i++){
	    		Map<String, Object> voObject = voZoneResult.get(i);
	    		int vnZoneIdOfRSM = Integer.parseInt(((BigDecimal) voObject.get("ZONE_ID")).toString());
	    		int vnRouteIdOfRSM = Integer.parseInt(((BigDecimal) voObject.get("ROUTE_ID")).toString());
	    		// select READER_ID in ROUTE
	    		List<Map<String, Object>> voReaderIdResult = voRouteManagement.findReaderIdByRoute(vnSiteId, vnZoneIdOfRSM, vnRouteIdOfRSM);
		    	for (int j = 0; j < voReaderIdResult.size(); j++){
		    		Map<String, Object> voObject2 = voReaderIdResult.get(j);
		    		vnReaderIdOfRoute = Integer.parseInt(((BigDecimal) voObject2.get("READER_ID")).toString());
		    	}
		    	voRSMManagement.updateReaderInRSM(vnSiteId, vnZoneIdOfRSM, vaPeriodRecentMonth, vaPeriodRecentYear, vnRouteIdOfRSM, vnReaderIdOfRoute);	    	
	    	}
		}
		db.close();
		prepareDataForSetReader(request);
    }
    
    /**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

}
